﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using SqlserverHelper;
using System.Threading;

using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using OJ;
using DLLmySQL;
using System.Text;
using System.IO;

// 流向数据-导出   8、销售数据导出
public partial class getDataSellToCSV : System.Web.UI.Page
{
    public string strCnn = ConfigurationSettings.AppSettings["DataBaseString"];
    protected void Page_Load(object sender, EventArgs e)
    {
        string customer_code = "";
        string shop_code = "";
        string prd_code = "";
        string day_begin = "";
        string day_end = "";
        string seller_code = "";
        string user_identity = "0";
        if (Request.Form["customer_code"] != null && Request.Form["customer_code"].ToString().Trim() != "")
        {
            customer_code = Request.Form["customer_code"].ToString();
        }
        if (Request.Form["shop_code"] != null && Request.Form["shop_code"].ToString().Trim() != "")
        {
            shop_code = Request.Form["shop_code"].ToString();
        }
        if (Request.Form["prd_code"] != null && Request.Form["prd_code"].ToString().Trim() != "")
        {
            prd_code = Request.Form["prd_code"].ToString();
        }
        if (Request.Form["day_begin"] != null && Request.Form["day_begin"].ToString().Trim() != "")
        {
            day_begin = Request.Form["day_begin"].ToString();
        } 
        if (Request.Form["day_end"] != null && Request.Form["day_end"].ToString().Trim() != "")
        {
            day_end = Request.Form["day_end"].ToString();
        }
        if (Request.Form["seller_code"] != null && Request.Form["seller_code"].ToString().Trim() != "")
        {
            seller_code = Request.Form["seller_code"].ToString();
        }
        if (Request.Form["user_identity"] != null && Request.Form["user_identity"].ToString().Trim() != "")
        {
            user_identity = Request.Form["user_identity"].ToString();
        }
        Define.retgetDataSellToCSV ret = null;

        if (day_begin == "" || day_end == "")
        {
            ret = new Define.retgetDataSellToCSV();
            ret.code = 40001;
            ret.msg = "缺少所需参数";

            Response.Write(Ojson.ObjectToJson(ret));
            return;
        }
        string stResult = f_getCSV(customer_code, shop_code, prd_code, day_begin, day_end, seller_code, user_identity);
        Response.Write(stResult);
    }

    public string f_getCSV(string customer_code, string shop_code, string prd_code, string day_begin, string day_end, string seller_code, string user_identity)
    {
        Define.retgetDataSellToCSV ret = new Define.retgetDataSellToCSV();
        string outputFile = "";
        string outputUrl = "https://" + Request.Url.Authority;
        try
        {
            // sb.Append("日期,销售方代码,销售方名称,采购方代码，采购方名称,产品代码,产品名称,产品规格,批号,数量,单位,单价,金额,生产厂商,批准文号");
            //string strQuery = "SELECT id,ymd,prd_code,prd_name,prd_spec,mfrs,prd_unit,tax_rate,retail_price,sell_qty,sell_price,sell_amt,dept_code,dept_name,dept_addr,order_id,order_line_id,prd_batch,period,pzwh,seller_code,seller_name,made_time,maker,updater_time,updater FROM dsn_sell WHERE  DATE_FORMAT(ymd,'%Y-%m-%d') >=  '" + day_begin.Trim() + "' and DATE_FORMAT(ymd,'%Y-%m-%d') <= '" + day_end.Trim() + "'";
            string strQuery = "SELECT ymd,seller_code,seller_name,purchaser_code,purchaser_name,prd_code,prd_name,prd_spec,prd_batch,prd_qty,prd_unit,prd_price,total,mfrs,pzwh FROM dsnjh WHERE 1=1 ";
            string strQuery1 = "SELECT ymd,seller_code,seller_name,purchaser_code,purchaser_name,prd_code,prd_name,prd_spec,prd_batch,prd_qty,prd_unit,prd_price,total,mfrs,sell_type,delivery_address,pzwh FROM dsnxs WHERE 1=1 ";
            string strQuery2 = "SELECT ymd,comp_code,comp_name,prd_code,prd_name,prd_spec,prd_batch,prd_qty,prd_unit,prd_price,period,inv_status,mfrs,pzwh FROM dsnkc WHERE 1=1 ";


            if (day_begin != "") { 
                strQuery = strQuery + " and ymd >= '" +day_begin.Trim()+ "'";
                strQuery1 = strQuery1 + " and ymd >= '" + day_begin.Trim() + "'";
                strQuery2 = strQuery2 + " and ymd >= '" + day_begin.Trim() + "'";
            }
            if (day_end != "") {
                strQuery = strQuery + " and ymd <= '" + day_end.Trim() + "'";
                strQuery1 = strQuery1 + " and ymd <= '" + day_end.Trim() + "'";
                strQuery2 = strQuery2 + " and ymd <= '" + day_end.Trim() + "'";
            }
            //if (shop_code != "") { 
            //    strQuery = strQuery + "and dept_name = '" +shop_code.Trim()+ "'";
            //}
            if(prd_code != "") {
                strQuery = strQuery + " and prd_name = (select product_name from dsh_prd where prd_code = '" + prd_code.Trim() + "')";
                strQuery1 = strQuery1 + " and prd_name = (select product_name from dsh_prd where prd_code = '" + prd_code.Trim() + "')";
                strQuery2 = strQuery2 + " and prd_name = (select product_name from dsh_prd where prd_code = '" + prd_code.Trim() + "')";
            }

            // 根据不同权限取不同数据
            if (user_identity != "")
            {
                strQuery = strQuery     + " and from_id = '" + user_identity.Trim() + "'";
                strQuery1 = strQuery1   + " and from_id = '" + user_identity.Trim() + "'";
                strQuery2 = strQuery2   + " and from_id = '" + user_identity.Trim() + "'";

                strQuery = strQuery + " and prd_name in ((select product_name from dsh_prd where user_identity = '" + user_identity.Trim() + "' ))";
                strQuery1 = strQuery1 + " and prd_name in ((select product_name from dsh_prd where user_identity = '" + user_identity.Trim() + "' ))";
                strQuery2 = strQuery2 + " and prd_name in ((select product_name from dsh_prd where user_identity = '" + user_identity.Trim() + "' ))";
                
            }

            //if (seller_code != "")
            //{
            //    strQuery = strQuery + "and seller_code = '" + seller_code.Trim() + "'";
            //}
            //strQuery = strQuery + " group by ymd,seller_name,purchaser_name,prd_name,prd_spec";
            // strQuery1 = strQuery1 + " group by ymd,seller_name,purchaser_name,prd_name,prd_spec";
            //strQuery2 = strQuery2 + " group by ymd,mfrs,comp_name,prd_name,prd_spec";


            LogBLL.log("strQuery=" + strQuery);
            LogBLL.log("strQuery1=" + strQuery1);
            LogBLL.log("strQuery2=" + strQuery2);
            DataTable DT = CmySQL.GetDataTbale(strCnn, strQuery);
            DataTable DT1 = CmySQL.GetDataTbale(strCnn, strQuery1);
            DataTable DT2 = CmySQL.GetDataTbale(strCnn, strQuery2);


            Define.getDataSellToCSV r = new Define.getDataSellToCSV();

            string fileName = "out_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".csv";
            outputFile = AppDomain.CurrentDomain.BaseDirectory + "out\\" + fileName;
            outputUrl = outputUrl + "/api_sst/out" + "/" + fileName;

            ExportToCsv(DT, DT1, DT2, outputFile);

            r.outputUrl = outputUrl;

            ret.code = 200;
            ret.msg = "success";
            ret.data = r;

            OperLog.log("流向数据-导出", user_identity);



        }
        catch(Exception e)
        {
            string er = e.Message;
            ret.code = 20000;
            ret.msg = er;
            ret.data = null;
        }
        return Ojson.ObjectToJson(ret);

    }

    /// <summary>
    /// DataTable导出成CSV文件
    /// </summary>
    /// <param name="dataTable">数据集</param>
    /// <param name="filePath">导出文件地址</param>
    public void ExportToCsv(DataTable dataTable,DataTable dataTable1,DataTable dataTable2, string filePath)
    {
        StringBuilder sb = new StringBuilder();
        //foreach (DataColumn col in dataTable.Columns)
        //{
        //    sb.Append(col.ColumnName + ",");
        //}
        sb.Append("进货");
        sb.AppendLine();
        //sb.Append("日期,供应商,客户,产品,规格,数量");
        sb.Append("日期,销售方代码,销售方名称,采购方代码,采购方名称,产品代码,产品名称,产品规格,批号,数量,单位,单价,金额,生产厂商,批准文号");
        // ymd,seller_name,purchaser_name,prd_name,prd_spec,prd_batch,prd_qty,prd_unit,prd_price,total

        sb.AppendLine();
        foreach (DataRow row in dataTable.Rows)
        {
            foreach (DataColumn col in dataTable.Columns)
            {
                sb.Append(row[col].ToString().Replace(",", " ") + ",");
            }
            sb.AppendLine();
        }

        sb.AppendLine();

        sb.Append("销售");
        sb.AppendLine();
        sb.Append("日期,销售方代码,销售方名称,采购方代码,采购方名称,产品代码,产品名称,产品规格,批号,数量,单位,单价,金额,生产厂商,销售类型,收货地址,批准文号");
        //sb.Append("日期,客户,门店,产品,规格,批号,数量,单位,单价,金额");

        sb.AppendLine();
        foreach (DataRow row in dataTable1.Rows)
        {
            foreach (DataColumn col in dataTable1.Columns)
            {
                sb.Append(row[col].ToString().Replace(",", " ") + ",");
            }
            sb.AppendLine();
        }

        sb.AppendLine();

        sb.Append("库存");
        sb.AppendLine();
        sb.Append("日期,公司代码,公司名称,产品代码,产品名称,产品规格,批号,数量,单位,单价,有效期,库存状态,生产厂商,批准文号");
        //sb.Append("日期,供应商,客户,产品,规格,数量");

        


        sb.AppendLine();
        foreach (DataRow row in dataTable2.Rows)
        {
            foreach (DataColumn col in dataTable2.Columns)
            {
                sb.Append(row[col].ToString().Replace(",", " ") + ",");
            }
            sb.AppendLine();
        }   
  
                            
        using (StreamWriter writer = new StreamWriter(filePath))
        {
            writer.Write(sb.ToString());
        }
    }
}